package com.wg.msshopsp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.wg.msshopsp.entity.ProductVO;
import com.wg.msshopsp.entity.Shop;
import com.wg.msshopsp.entity.ShopScoreVO;
import com.wg.msshopsp.entity.ShopUserDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ShopMapper extends BaseMapper<Shop> {
    //查询店铺详情页所有东西
    @Select("SELECT " +
            "p.id AS product_id, " +
            "p.shop_id, " +
            "p.name, " +
            "p.main_img, " +
            "p.price_before AS priceBefore, " +
            "p.price_after AS priceAfter, " +
            "p.status, " +
            "s.create_time AS createTime, " +
            "p.type, " +
            "s.shop_name AS shopName, " +
            "COUNT(od.id) AS orderCount, " +
            "(SELECT COALESCE(AVG(r.score), 0) " +
            " FROM product p2 " +
            " LEFT JOIN review r ON p2.id = r.product_id " +
            " WHERE p2.shop_id = s.id) AS shopScore, " +
            "(SELECT COUNT(*) FROM collection WHERE shop_id = s.id) AS fanCount " +
            "FROM product p " +
            "JOIN shop s ON p.shop_id = s.id " +
            "LEFT JOIN orderdetails od ON p.id = od.product_id " +
            "WHERE s.id = #{shopId} " +
            "GROUP BY p.id, p.shop_id, p.name, p.main_img, p.price_before, p.price_after, p.status, s.create_time, p.type, s.shop_name")
    List<ProductVO> selectProductsByShopId(@Param("shopId") Integer shopId);


    @Select("SELECT " +
            "shop.id, " +
            "shop.user_id AS userId, " +
            "shop.shop_name AS shopName, " +
            "shop.logo, " +
            "shop.shop_desc AS shopDesc, " +
            "shop.status AS shopStatus, " +
            "shop.create_time AS createTime, " +
            "shop.product_count AS productCount, " +
            "user.avatar, " +
            "user.nc, " +
            "user.phone, " +
            "user.status AS userStatus " +
            "FROM shop " +
            "JOIN user ON shop.user_id = user.id")
    IPage<ShopUserDTO> selectShopWithUser(IPage<ShopUserDTO> page);

    // 查询店铺，带评分
    @Select("SELECT " +
            "  s.shop_name AS shopName, " +
            "  s.shop_desc AS shopDesc, " +
            "  s.create_time AS createTime, " +
            "  COALESCE(AVG(r.score), 0) AS avgScore " +
            "FROM " +
            "  shop s " +
            "LEFT JOIN product p ON s.id = p.shop_id " +
            "LEFT JOIN review r ON p.id = r.product_id " +
            "WHERE s.id = #{shopId} " +
            "GROUP BY " +
            "  s.id, s.shop_name, s.shop_desc, s.create_time")
    ShopScoreVO selectShopWithAvgScoreById(@Param("shopId") Integer shopId);
}
